--Function of checking reference value
create or replace procedure p_check_reference_value(
    --Column id
    a_column_id raw,
    --Reference value
    a_ref_value raw)
as
    --Ref table name
    a_ref_table_name varchar2(26);
    --Row count
    row_count number;
begin     
     --If value is null there is no need for any check
     if a_ref_value is null then
        return;
     end if;
     
     --Enumerating all tables that the column references 
     for val in (select ref_table_id from meta_ref_targets
         where column_id = a_column_id) loop
         
         --Reading ref table name
         select table_name into a_ref_table_name
         from meta_tables
         where table_id = val.ref_table_id;
         
         --Searching for referenced row
         execute immediate 'select count(*) from tbl_'
         || a_ref_table_name
         || ' where id=:1'
         into row_count
         using a_ref_value;
         
         --If referenced row was found
         if row_count > 0 then
            return;
         end if;         
     end loop;
     
     --Referenced row was not found
     raise_application_error(-20024,
                'A row that was referenced by the column was not found (column_id = '
                || a_column_id
                || ', ref_value = '
                || a_ref_value
                || ') (#error_code = 20024)');
end;

--go

--Function of creating check ref value trigger
create or replace procedure p_create_ref_checking(
    --Column id
    a_column_id raw)
as
    --Table id
    a_table_id raw(16);
    --Column name
    a_column_name varchar2(26);
    --Table name
    a_table_name varchar2(26);
    --Trigger name
    a_trigger_name varchar2(30);
begin
    --Reading column properties
    select column_name, table_id
    into a_column_name, a_table_id
    from meta_ref_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
 
    --Creating trigger of checking column value
    a_trigger_name := 'r' || substr(a_column_id, 1, 29);
    
    execute immediate 'create or replace trigger ' || a_trigger_name
    || ' after insert or update of ' || a_column_name
    || ' on tbl_' || a_table_name
    || ' for each row'
    || ' declare pragma autonomous_transaction;'
    || ' begin'
    || ' p_check_reference_value('''
    || a_column_id || ''', :new.'
    || a_column_name || '); end;';
    
end;

--go

--Function of dropping check ref value trigger
create or replace procedure p_drop_ref_checking(
    --Column id
    a_column_id raw)
as
begin    
    execute immediate 'drop trigger ' || 'r' || substr(a_column_id, 1, 29);
end;

--go


--Function of adding reference column
create or replace procedure meta_add_ref_column(
    --Table id
    a_table_id raw,
    --Column id
    a_column_id raw,
    --Column name
    a_column_name varchar2)
as
    --Table name
    a_table_name varchar2(26);
begin
    p_check_col_not_exists(a_table_id, a_column_id, a_column_name);
    
     a_table_name :=  p_get_table_name(a_table_id);
     
    --Changing the metadata
    insert into meta_ref_columns(column_id, table_id, column_name,
        order_index, customization_level)
    values (a_column_id, a_table_id, a_column_name,
        p_generate_order_index(a_table_id), 1);
        
    --Dropping table view
    p_drop_view_if_exists(a_table_name);
         
    --Creating new column
    execute immediate 'alter table tbl_' 
    || a_table_name
    || ' add ' || a_column_name
    || ' raw(16)';
         
    --Creating trigger of checking column value
    p_create_ref_checking(a_column_id);
         
    --Creating table view
    p_create_table_view(a_table_id);
end;

--go

--Function of setting column default value
create or replace procedure meta_set_ref_default(
    --Column id
    a_column_id raw,
    --Default value
    a_default_value raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Column name
    a_column_name varchar2(26);
begin
    p_check_col_type(a_column_id, 'meta_ref_columns', 'reference');
    
    --Reading column properties
    select table_id, column_name
    into a_table_id, a_column_name
    from meta_ref_columns
    where column_id = a_column_id;
    
    a_table_name := p_get_table_name(a_table_id);
    
     if a_default_value is not null then
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default ''' || a_default_value || ''')';
    else
        execute immediate 'alter table tbl_' 
        || a_table_name
        || ' modify (' || a_column_name
        || ' default null)';
    end if;
    
    --Updating the metadata
    update meta_ref_columns
    set default_value = a_default_value
    where column_id = a_column_id;
end;

--go

--Function of adding ref target
create or replace procedure meta_add_ref_target(
    --Column id
    a_column_id raw,
    --Ref table id
    a_ref_table_id raw)
as
    --Row count
    a_row_count number;
begin
    p_check_col_type(a_column_id, 'meta_ref_columns', 'reference');
    
    select count(*) into a_row_count
    from meta_ref_targets
    where column_id = a_column_id and ref_table_id = a_ref_table_id;
    
    if a_row_count > 0 then
        raise_application_error(-20022,
                'Adding duplicate reference target is forbidden (column_id = '
                || a_column_id
                || ', ref_table_id = '
                || a_ref_table_id
                || ') (#error_code = 20022)');
    end if;
     
    insert into meta_ref_targets (column_id, ref_table_id)
    values (a_column_id, a_ref_table_id);
end;

--go

--Function of removing ref target
create or replace procedure meta_delete_ref_target(
    --Column id
    a_column_id raw,
    --Ref table id
    a_ref_table_id raw)
as
    --Table id
    a_table_id raw(16);
    --Table name
    a_table_name varchar2(26);
    --Ref column name
    a_ref_column_name varchar2(26);
    --Ref table name
    a_ref_table_name varchar2(26);
    --Row count
    a_row_count number;
begin
    --Searching for the target
    select count(*) 
    into a_row_count
    from meta_ref_targets
    where column_id = a_column_id
    and ref_table_id = a_ref_table_id;
    
    if a_row_count = 0 then
        raise_application_error(-20023,
                'Deleting nonexistent reference target is forbidden (column_id = '
                || a_column_id
                || ', ref_table_id = '
                || a_ref_table_id
                || ') (#error_code = 20023)');
    end if;

    --Reading ref column properties
    select column_name, table_id
    into a_ref_column_name, a_table_id
    from meta_ref_columns
    where column_id = a_column_id;
        
    a_table_name := p_get_table_name(a_table_id);
    a_ref_table_name := p_get_table_name(a_ref_table_id);    

    execute immediate 'update tbl_'
    || a_table_name || ' parent'
    || ' set ' || a_ref_column_name || ' = null'
    || ' where exists('
    || 'select id from tbl_' || a_ref_table_name
    || ' where id = parent.' || a_ref_column_name || ')';

    delete from meta_ref_targets
    where column_id = a_column_id
    and ref_table_id = a_ref_table_id;
    
end;

--go

-- Function of setting the need to check reference values
create or replace procedure meta_set_ref_checking(
    --Column id
    a_column_id raw,
    --The need to check reference values
    a_ref_checking number)
as
    --Trigger name
    a_trigger_name varchar2(30);
begin
    p_check_col_type(a_column_id, 'meta_ref_columns', 'reference');
     
    --Reference checking trigger name
    a_trigger_name := 'r' || substr(a_column_id, 1, 29);
    
    if a_ref_checking = 1 then
        execute immediate 'alter trigger ' || a_trigger_name || ' enable';
    else
        execute immediate 'alter trigger ' || a_trigger_name || ' disable';
    end if;
    
    --Updating the metadata
    update meta_ref_columns
    set ref_checking = a_ref_checking 
    where column_id = a_column_id;
end;

--go